package com.test.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONArray;

public class FetchData 
{
	public static synchronized List<String> loadDataIntoCollection(String deviceId)
	{
		List<String> coordinates = new ArrayList<String>();
		try 
		{
			Connection connection =  DataSource.getConnection();
			PreparedStatement pstmt = connection.prepareStatement("SELECT DISTINCT latitude,langitude,ENTEREDDTTIME FROM GPS_LOG_INFO WHERE imenumber='"+deviceId+"' AND [entereddttime] between dateadd(DAY, -2, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP ORDER BY entereddttime ASC");
			//PreparedStatement pstmt = connection.prepareStatement("SELECT DISTINCT LAT,LANG FROM DUMMY_COORDINATES WHERE deviceId='"+deviceId+"' ORDER BY DATE_TIME ASC");
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				coordinates.add(rs.getString(1)+","+rs.getString(2));
			}
			connection.close();
		} 
		catch (Exception e) {		e.printStackTrace();}
		return coordinates;
	}

	public static JSONArray getCoordinates(String deviceId)
	{
		try {
			JSONArray mJSONArray = new JSONArray();
			mJSONArray.addAll(loadDataIntoCollection(deviceId));
			return mJSONArray;
		} catch (Exception e) {
			return null;
		}
	}
	
//	public static String getLatestCoordinates(String deviceId)
//	{
//		String coordinates = new String();
//		try 
//		{
//			Connection connection =  DataSource.getConnection();
//			PreparedStatement pstmt = connection.prepareStatement("SELECT TOP 1 * FROM GPS_LOG_INFO WHERE imenumber='"+deviceId+"' ORDER BY ENTEREDDTTIME DESC");
//			ResultSet rs = pstmt.executeQuery();
//			if(rs.next())
//			{
//				System.out.println(rs.getString(2)+","+rs.getString(1));
//				coordinates = rs.getString(2)+","+rs.getString(1);
//			}
//			
//			connection.close();
//		} 
//		catch (Exception e) {		e.printStackTrace();}
//		return coordinates;
//	}
	
	public static String getLatestCoordinates(String deviceId,String fake)
	{
		if(fake!=null)
		{
			return getFakeCoordinates(deviceId);
		}
		else
		{
			String coordinates = new String();
			try 
			{
				Connection connection =  DataSource.getConnection();
				PreparedStatement pstmt = connection.prepareStatement("SELECT TOP 1 * FROM GPS_LOG_INFO WHERE imenumber='"+deviceId+"' ORDER BY ENTEREDDTTIME DESC");
				ResultSet rs = pstmt.executeQuery();
				if(rs.next())
				{
					System.out.println(rs.getString(2)+","+rs.getString(1));
					coordinates = rs.getString(2)+","+rs.getString(1);
				}
				
				connection.close();
			} 
			catch (Exception e) {		e.printStackTrace();}
			return coordinates;
		}
		
	}
	
	private static int i=0;
	private static List<String> coordinatesList = new ArrayList<String>();
	public static synchronized String getFakeCoordinates(String deviceId)
	{
		System.out.println("                           "+i);
		String coordinates = new String();
		try 
		{
			if(i==0 && coordinatesList.isEmpty())
			{
				Connection connection =  DataSource.getConnection();
				PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM GPS_LOG_INFO WHERE imenumber='"+deviceId+"' ORDER BY ENTEREDDTTIME ASC");
				ResultSet rs = pstmt.executeQuery();
				while(rs.next())
				{
					coordinatesList.add(rs.getString(2)+","+rs.getString(1));
				}
				connection.close();
			}
			
			return coordinatesList.get(i++);
		
		} 
		catch (Exception e) {	i=1; e.printStackTrace();}
		return coordinates;
	}
}
